package com.dmgis.qxfw.controller;


import java.io.ByteArrayInputStream;
import java.io.ObjectInputStream;
import java.net.URLDecoder;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.util.StringUtil;

import com.alibaba.fastjson.JSONObject;
import com.dmgis.qxfw.domain.RequestDataByDelete;
import com.dmgis.qxfw.domain.RequestDataByInsert;
import com.dmgis.qxfw.domain.RequestDataBySelect;
import com.dmgis.qxfw.domain.RequestDataByUpdate;
import com.dmgis.qxfw.utils.JsonUtil;

public class Handle {

	
	public String getData(HttpServletRequest request,String tableName) throws Exception{
		String param = request.getParameter("param");
		if(!param.startsWith("{"))
			param= URLDecoder.decode(param,"UTF-8");
		RequestDataBySelect dataselect=null;
		if(param!=null&&param.length()>0){
			//dataselect = (RequestDataBySelect) deserializeToObject(param);
			dataselect = JSONObject.parseObject(param, RequestDataBySelect.class);
		}
		StringBuilder sql = new StringBuilder();
		String groupStr = " group by ";//group条件
        String orderStr = " order by ";
        String havingStr = "";
        sql.append("select");
        int count = 0;
        int groupCount = 0;
        String[] columns = null;
        String[] sort = null;
        String queryKey = null;
        int limit = 0;
        if(dataselect!=null){
        	columns = dataselect.getColumns();
        	sort = dataselect.getSort();
        	queryKey = dataselect.getParam();
        	limit = dataselect.getLimit();
        	havingStr = dataselect.getHaving();
        }
        if(columns == null||columns.length == 0){
        	sql.append("*");
        }else{
        	for(String column : columns){
        		sql.append(" "+column+", ");
        		if(column.contains("max(") || column.contains("min(") ||
        				column.contains("avg(") || column.contains("sum(")||column.contains("count(")){
        			count++;
        		}else{
        			groupCount++;
        			if (column.contains(" as "))
                    {
                        groupStr += column.substring(0, column.indexOf(" as ")) + ",";
                    }
        			 else if (column.startsWith("distinct "))
                     {
                         groupStr += column.replace("distinct ","") + ",";
                     }
                    else
                    {
                        groupStr += column + ",";
                    }
        		}
        	}
        	if(sql.toString().trim().endsWith(",")){
        		String strSql = sql.toString().trim().substring(0, sql.toString().trim().length()-1);
        		sql = new StringBuilder(strSql);
        	}
        }
        sql.append(" from "+ tableName);
        //where 字段
        if(queryKey!=null&&queryKey.length()>0){
        	sql.append(" where " + queryKey);
        }
        //group 字段
        if(count>0&&groupCount>0){
        	if(groupStr.endsWith(",")){
        		if(groupStr.contains("distinct"))
       			groupStr=groupStr.replace("distinct", "");
        		groupStr = groupStr.substring(0,groupStr.length()-1);
        	}
        	sql.append(groupStr);
        }
        if (havingStr!=null&&!havingStr.equals("")) {
			sql.append(" having "+havingStr);
			}
        //order 字段
        if(sort != null && sort.length > 0){
        	for(String order : sort){
        		orderStr += order + ",";
        	}
        	if(orderStr.endsWith(",")){
        		orderStr = orderStr.substring(0, orderStr.length()-1);
        	}
        	sql.append(orderStr);
        }
    	
        //limit字段
        if(limit > 0){
        	sql.append(" limit " + limit);
        }
		return sql.toString();
	}
	
	
	
	
	public String delData(HttpServletRequest request,String tableName) throws Exception{
        StringBuilder sql = new StringBuilder();
        String param = request.getParameter("param");
        RequestDataByDelete delete = new RequestDataByDelete();
        if(param!=null&&param.length()>0){
//        	delete = JSONObject.parseObject(param,RequestDataByDelete.class);
        	delete = JsonUtil.jsonStr2Object(param, RequestDataByDelete.class);
        }else{
        	throw new Exception("删除参数为空！");
        }
    	String queryKey = delete.getParam();
    	//添加where字段
    	if(queryKey!=null&&queryKey.length()>0){
    		sql.append("delete from "+tableName);
            sql.append(" where " + queryKey + ";");
    	}
		return sql.toString();
	}
	
	
	
	
	public String updateData(HttpServletRequest request,String tableName) throws Exception{
		StringBuilder sql = new StringBuilder();
        sql.append("update "+tableName);
        
        String param = request.getParameter("param");
        RequestDataByUpdate requestData = new RequestDataByUpdate();
        if(param!=null&&param.length()>0){
        	requestData = JSONObject.parseObject(param,RequestDataByUpdate.class);
        }else{
        	throw new Exception("更新参数为空！");
        }
        String queryKey = requestData.getParam();
        HashMap<String ,Object> map = requestData.getData();
        //获取set字符串
        sql.append(getSetString(map));
        //添加where字段
        if (queryKey!=null&&queryKey.length()>0)
        {
            sql.append(" where " + queryKey);
        }
		return sql.toString();
	}
	
	
	
	public String insertData(HttpServletRequest request,String tableName) throws Exception{
		StringBuilder sql = new StringBuilder();
//		sql.append("insert into "+tableName);
        String param = request.getParameter("param");
        RequestDataByInsert requestData = new RequestDataByInsert();
        if(param!=null&&param.length()>0){
        	requestData = JSONObject.parseObject(param, RequestDataByInsert.class);
        }else{
        	throw new Exception("插入参数为空");
        }
        List<HashMap<String, Object>> data = requestData.getData();
        //获取insert字符串
        sql.append(getInsertString(data,tableName));
		return sql.toString();
	}
	
	
	
	//反序列化
	public static Object deserializeToObject(String str) throws Exception{
        ByteArrayInputStream byteIn = new ByteArrayInputStream(str.getBytes("ISO-8859-1"));  
        ObjectInputStream objIn = new ObjectInputStream(byteIn);  
        Object obj =objIn.readObject();  
        return obj;  
   }
	
	
	
	
	//拼接set字段
	public String getSetString(HashMap<String, Object> map) throws Exception{
		String str = " set";
		if(map!=null){
			for(Entry<String,Object> entry : map.entrySet() ){
				if(entry.getValue()!=null&&entry.getValue().toString().length()>0){
					if(entry.getValue().getClass().equals(String.class)){
						str += " " + entry.getKey()+ "='" + entry.getValue().toString().trim() + "',";
					}else{
						str += " " + entry.getKey()+ "=" + entry.getValue() + ",";
					}
				}else{
					continue;
				}
				
			}
		}
		if(str.endsWith(",")){
			str = str.substring(0,str.length()-1);
		}
		return str;
		
	}
	//拼接insert
	public String getInsertString(List<HashMap<String, Object>> data,String tableName) throws Exception{
		StringBuilder strInsert = new StringBuilder();
        if(data!=null&&data.size()>0){
        	for(HashMap<String, Object> map : data){
        		strInsert.append("insert into "+ tableName);
        		String columns = " (";
        		String values = " values(";
        		for(Entry<String, Object> entry : map.entrySet()){
        			if(entry.getValue()!=null&&entry.getValue().toString().length()>0)
        			{
        				columns += entry.getKey() + ",";
        				if(entry.getValue().getClass().equals(String.class)||
        						entry.getValue().getClass().equals(Date.class)){
        					values += "'"+entry.getValue()+"',";
        				}else{
        					values += entry.getValue()+",";
        				}
        			}
        		}
//        		for(Entry<String, Object> entry : map.entrySet()){
//        			if(entry.getKey()!=null&&entry.getKey().length()>0){
//        				columns += entry.getKey() + ",";
//        			}else{
//        				continue;
//        			}
//        			if(entry.getValue()!=null&&entry.getValue().toString().length()>0){
//        				if(entry.getValue().getClass().equals(String.class)||
//        						entry.getValue().getClass().equals(Date.class)){
//        					values += "'"+entry.getValue()+"',";
//        				}else{
//        					values += entry.getValue()+",";
//        				}
//        			}else{
//        				values += "NULL,";
//        			}
//        		}
        		if(columns.trim().endsWith(",")){
        			columns = columns.trim().substring(0,columns.length()-2) + ")";
        		}
        		if(values.trim().endsWith(",")){
        			values = values.trim().substring(0,values.length()-2) + ");";
        		}
        		strInsert.append(columns+values);
        	}
        }
		return strInsert.toString();
		
	}
}
